﻿using BySoft.Server.Common;
using BySoft.Server.Common.Entity;
using OneSoft.DataAccess;
using OneSoft.EntityCore;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace BySoft.Server.DataAccess
{
    public partial class BsKnifeuseDA
    {
        public static DataTable SelectList(string strBillID, string strKnifeID,
            string strFrom, string strEnd, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"SELECT a.*,d.detailID,
  
       d.Nums,d.ReturnDate, d.ReturnUserID,su.UserName,
      
       d.KnifeID,
       m.KnifeName,
       m.Specifications,
       m.Unit,
       m.BelongCustomer, m.Factory, m.SafeStock,
       mt.KnifeTypeName 
FROM BS_KnifeUse AS a
   LEFT JOIN BS_KnifeUseDetail AS d ON d.BillID = a.BillID
   LEFT JOIN md_Knife m
            ON  d.KnifeID = m.KnifeID
       LEFT JOIN md_KnifeType mt
            ON  m.KnifeTypeID = mt.KnifeTypeID
            
      LEFT JOIN SYS_User AS su ON d.ReturnUserID=su.UserID  

  WHERE a.[Status]='1' ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();
                if (!string.IsNullOrEmpty(strBillID))
                {
                    strcmd += " and a.BillID like @strBillID";
                    dpc.AddWithValue("@strBillID", "%" + strBillID + "%");
                }

                if (!string.IsNullOrEmpty(strKnifeID))
                {
                    strcmd += " and d.KnifeID like @strKnifeID";
                    dpc.AddWithValue("@strKnifeID", "%" + strKnifeID + "%");
                }


                if (!string.IsNullOrEmpty(strFrom))
                {
                    strcmd += " and a.BillDate >= @strFrom";
                    dpc.AddWithValue("@strFrom", strFrom);
                }
                if (!string.IsNullOrEmpty(strEnd))
                {
                    strcmd += " and a.BillDate <= @strEnd";
                    dpc.AddWithValue("@strEnd", strEnd);
                }
                strcmd += " order by a.BillDate desc";



                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 排程单中没有结案的才能领用
        /// 
        /// </summary>
        /// <param name="strKnifeID"></param>
        /// <param name="strWorkOrderNo"></param>
        /// <param name="pageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="m_count"></param>
        /// <returns></returns>
        public static DataTable SelectCanUseKnife(string strCurrentID, string strKnifeID, string strKnifeTypeID,
            string strWorkOrderNo, int pageIndex, int PageSize, out int m_count)
        {
            try
            {
                string strcmd = @"SELECT bb.*,bbk.KnifeID,bbk.Nums,
  mk.KnifeName, mk.Specifications, mk.BelongCustomer,
  mk.Factory, mk.KnifeStatus,mk.Unit,
  mt.KnifeTypeName,mt.KnifeTypeID,
  mp.ProductName,mp.Specifications as ProductSpecifications
   
 FROM BS_BOM AS bb
 LEFT JOIN bs_bomknife bbk ON bb.BOMID = bbk.BOMID
 LEFT JOIN MD_Knife AS mk ON mk.KnifeID = bbk.KnifeID
      LEFT JOIN md_KnifeType mt
            ON  mk.KnifeTypeID = mt.KnifeTypeID
 LEFT JOIN BS_ScheduleDetail AS bsd ON bsd.WorkOrderNo=bb.WorkOrderNo
 LEFT JOIN MD_Product AS mp ON mp.ProductID=bb.ProductID
 LEFT JOIN  (
        
        ---已有数量
          SELECT KnifeID,sum(Nums) as returnNums 
            from Bs_KnifeUseDetail aR 
            where aR.BillID<>@CurrentBillID
             group by  KnifeID
             )  ret on ret.KnifeID=bbk.KnifeID    

WHERE 1=1 
--and isnull(bsd.isFinish,'') <>'1'
AND isnull(mk.KnifeStatus,'')='1'

   ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();


                dpc.AddWithValue("@CurrentBillID", strCurrentID);

                if (!string.IsNullOrEmpty(strKnifeTypeID))
                {
                    strcmd += " and mt.KnifeTypeID = @strKnifeTypeID";
                    dpc.AddWithValue("@strKnifeTypeID", strKnifeTypeID);
                }

                if (!string.IsNullOrEmpty(strKnifeID))
                {
                    strcmd += " and mk.KnifeID like @strKnifeID";
                    dpc.AddWithValue("@strKnifeID", "%" + strKnifeID + "%");
                }

                if (!string.IsNullOrEmpty(strWorkOrderNo))
                {
                    strcmd += " and bb.WorkOrderNo = @strWorkOrderNo";
                    dpc.AddWithValue("@strWorkOrderNo", strWorkOrderNo);
                }


                strcmd += " order by mk.KnifeID desc";



                DataTable dt = DataAccessCommon.ExecuteDataTablePaging(strcmd, pageIndex, PageSize, dpc, out m_count);


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 归还人
        /// 
      
        public static DataTable SelectReturnUser()
        {
            try
            {
                string strcmd = @"SELECT userID,su.UserName FROM SYS_User AS su

WHERE ISNULL(EmployeeType,'')<>'3'
AND su.[Status]='1'

   ";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();


               

                strcmd += " order by UserName ";



                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];


                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }




        #region View Data
        public static DataTable SelectHeadData(string strBillID)
        {
            try
            {
                string strcmd = @"SELECT a.* ,                           
    
-1 as IsNew,-1 as IsDel
from Bs_KnifeUse a
  

 
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                DataTable dt = DataAccessCommon.Select(strcmd, dpc).Tables[0];
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public static DataTable SelectDetailData(string strBillID)
        {
            try
            {
                string strcmd = @"SELECT a.*,
       m.KnifeName,
       m.Specifications,
       m.Unit,
       m.BelongCustomer, m.Factory, m.SafeStock,
       mt.KnifeTypeName,
       -1  AS IsNew,
       -1  AS IsDel
FROM   Bs_KnifeUseDetail a
       LEFT JOIN md_Knife m
            ON  a.KnifeID = m.KnifeID
       LEFT JOIN md_KnifeType mt
            ON  m.KnifeTypeID = mt.KnifeTypeID
where a.BillID=@BillID";

                DataAccessParameterCollection dpc = new DataAccessParameterCollection();

                dpc.AddWithValue("@BillID", strBillID);
                return DataAccessCommon.Select(strcmd, dpc).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        #endregion



        #region AUD
        public static bool SaveOrUpdate(BsKnifeuseEntity entity, DataTable KnifeuseDetailDT, bool isNew)
        {
            bool istag = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                broker.BeginTransaction();
                try
                {
                    if (isNew)
                    {
                        DataAccessCommon.Insert(entity, broker);
                    }
                    else
                    {
                        DataAccessCommon.UpdateOnly(entity, broker);
                        ClsCommDA.Del("BS_KnifeUseDetail", "BillID", entity.BillID, broker);//delete  first  & then insert new 


                    }
                    if (KnifeuseDetailDT != null && KnifeuseDetailDT.Rows.Count > 0)
                    {
                        foreach (DataRow drItem in KnifeuseDetailDT.Rows)
                        {
                            BsKnifeusedetailEntity DetailEntity = new BsKnifeusedetailEntity();
                            DetailEntity.BillID = entity.BillID;
                            DetailEntity.KnifeID = CommonMethod.GetString(drItem["KnifeID"]);
                            DetailEntity.Nums = CommonMethod.ConvertToDecimal(drItem["Nums"]);

                            DetailEntity.ReturnUserID = CommonMethod.GetString(drItem["ReturnUserID"]);

                            string date = CommonMethod.GetString(drItem["ReturnDate"]);
                            if (!string.IsNullOrEmpty(date)) DetailEntity.ReturnDate = DateTime.Parse(date);
                        

                            //string strIsNew = CommonMethod.GetString(drItem["IsNew"]);
                            //string strIsDel = CommonMethod.GetString(drItem["IsDel"]);
                            if (isNew)//insert
                            {
                                DataAccessCommon.Insert(DetailEntity, true, broker);
                            }
                            else
                            {
                                
                                    DataAccessCommon.Insert(DetailEntity, true, broker);

                            


                            }

                        }
                    }
                    broker.Commit();
                }
                catch
                {
                    istag = false;
                    broker.Rollback();
                    throw;
                }
                return istag;
            }
        }

        public static bool Del(string strCode)
        {
            bool isResult = true;
            using (DataAccessBroker broker = DataAccessFactory.Instance())
            {
                try
                {
                    DataAccessCommon.Delete(strCode, "BillID", "BS_KnifeuseDetail");
                    DataAccessCommon.Delete(strCode, "BillID", "BS_Knifeuse");
                    broker.Commit();
                }
                catch (Exception ex)
                {
                    isResult = false;
                    broker.Rollback();
                    throw ex;
                }
            }
            return isResult;
        }
        #endregion
    }
}
